package com.hh.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.text.SimpleDateFormat;

public class HuLiDao {

    public final static String cfn = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    public final static String url = "jdbc:sqlserver://212.129.155.194:1433;DatabaseName=jcyy_test";
    public final static String user = "sa";
    public final static String password = "ZJzhengg@163.com";
    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    public JSONArray getHuLiList(String key, String pageSize, String mzhm ) {
        Connection con = null;//再写一个sql查询总页数  完了把这俩个封装到一起在返回
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(cfn);
            con = DriverManager.getConnection(url, user, password);

            if(!pageSize.equals("")){
            String sql = " select * from (select ROW_NUMBER() OVER(Order by ZXXH) AS RowNumber,* from PD_XMYY where mzy=3 ";
            if(mzhm!=null && !mzhm.equals("")){
                sql += "and MZHM ="+"mzhm";
            }
            if (key != null && !key.equals("")) {
                sql += " and XMMC LIKE '%" + key + "%' OR  PD_XMYY.ZXRY LIKE '%" + key + "%'";
            }
            
            int page = Integer.parseInt(pageSize);
            sql += ") as b where b.RowNumber BETWEEN (" + page + "-1)*6+1 and " + page + "*6 order by ZXXH asc; ";
            System.out.println(sql);
            statement = con.prepareStatement(sql);
            }
            else {
                String sql = "select * from PD_XMYY p where mzy=3  ";
                if(mzhm!=null && !mzhm.equals("")){
                    sql += "and MZHM ="+mzhm+" Order by p.zxrq desc";
                }
                System.out.println(sql);
                //如果参数传入没有问题的话打印的sql语句中应该是带有传进的参数的
                //举例 ：select * from PD_XMYY p where mzy=3  and MZHM =000101892700 Order by p.zxrq desc
                statement = con.prepareStatement(sql);
            }

            res = statement.executeQuery();

            while (res.next()) {
                JSONObject o = new JSONObject();
                o.put("xmmc", res.getString("xmmc"));
                o.put("zxry", res.getString("zxry"));
                Date zxrq = res.getDate("zxrq");
                if(zxrq==null){
                    o.put("zxrq", "null");
                }else{
                    String time = simpleDateFormat.format(zxrq);
                    o.put("zxrq", time);
                }
                o.put("yzzt", res.getString("yzzt"));
                o.put("wcbz", res.getInt("wcbz"));
                array.add(o);
            }
            HuLiToTalDao huLiToTalDao = new HuLiToTalDao();
            array.add(huLiToTalDao.getHuLiListToTal());
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            try {
                if (res != null) res.close();
                if (statement != null) statement.close();
                if (con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
    }


}

